Hello,
I have a massive table in my database, about 1.5TB and around 3.5 billion rows. (that is one partition of massive table). When I try to do backup, just on this one big table, after a while I get following error:
pg_dump: Dumping the contents of table "ta_low" failed: PQgetResult() failed.
pg_dump: Error message from server: ERROR: compressed data is corrupted
pg_dump: The command was: COPY public.ta_low (foreign_id, name, numbers) TO stdout;
where foreign_id is bigint, name is varchar(10) and extended storage, numbers is quite long array of ints, also extended storage obviously. The table works normally, I guess there will be small number of rows that are corrupted.
I tried using approach described here: https://no0p.github.io/postgresql/2013/04/02/postgres-corruption-resolution.html
However upon creating the checking function and running the query: select ctid from ta_low where chk(ta_low); I get another error, despite the exception handling in the function:
ERROR: compressed data is corrupted
CONTEXT: PL/pgSQL function chk(anyelement) while storing call arguments into local variables
********** Error **********
ERROR: compressed data is corrupted
SQL state: XX000
Context: PL/pgSQL function chk(anyelement) while storing call arguments into local variables
I want to backup this table and possibly replace the corrupted rows, although it is not top priority, and if single digits of rows are not working out of the billions its not a big issue. I'm using Postgresql 9.5. Any advice on how to find/replace/delete the corrupted rows is appreciated, or how to backup the table. Thank you.